﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBAccess;
using System.Data;
using SSPharma.Common;
using System.Data.Common;
using SSPharma.Model;

namespace SSPharma.Biz.Common
{
    class BizCommon
    {
        /// <summary>
        /// User:TAMNN ; 18/07/2014
        /// Mục đích: Tạo công tơ tự động theo từng Moule.
        /// </summary>
        /// <param name="p_sMaTienTo">"BP"</param>
        /// <param name="p_sDonVi">01</param>
        /// <param name="p_sBoPhan">01</param>
        /// <param name="p_sNam">2014</param>
        /// <param name="p_sThang">07</param>
        /// <returns>BP.01.01.14.07</returns>
        public string fs_CreateCodeId(string p_sMaTienTo,string p_sDonVi="",string p_sBoPhan ="",string p_sNam= "",string p_sThang ="")
        {
            Int64 p_CodeId = -1;
            // lấy số tăng tự động.
            SQLServer.Param[] Params = new SQLServer.Param[5];
            Params[0] = new SQLServer.Param("@p_sTienTo", p_sMaTienTo.Split('_').First());
            Params[1] = new SQLServer.Param("@p_sDonVi", p_sDonVi);
            Params[2] = new SQLServer.Param("@p_sBoPhan", p_sBoPhan);
            Params[3] = new SQLServer.Param("@p_iNam",p_sNam);
            Params[4] = new SQLServer.Param("@p_iThang", p_sThang);
            DataTable p_dtCodeId = Global.DataFactory.ReturnDataTable("Create_CodeID", CommandType.StoredProcedure, Params);
            if (p_dtCodeId!=null && p_dtCodeId.Rows.Count > 0)
                p_CodeId = Convert.ToInt64(p_dtCodeId.Rows[0]["CodeID"].ToString());
            else
                return "";
            //
            // lấy cấu trúc định dạng công tơ
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select top(1) KSDonVi,KSBoPhan,KSTuDong,KSThang,KSNam,KyTuPhanCach1,KyTuPhanCach2,KyTuPhanCach3,KyTuPhanCach4,KyTuPhanCach5");
            strSQL.Append(" from HT_DinhDangCongTo a where a.MaTienTo+'_'+convert(nvarchar(20),CounterId) = @p_sMaTienTo");
            Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@p_sMaTienTo", p_sMaTienTo);
            DataTable p_dtCauTruc = Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
            if (p_dtCauTruc !=null && p_dtCauTruc.Rows.Count > 0)
            {
                string p_sCauTruc = string.Empty;
                p_sCauTruc += !string.IsNullOrEmpty(p_sMaTienTo) ? (p_sMaTienTo.Split('_').First() + p_dtCauTruc.Rows[0]["KyTuPhanCach1"].ToString()) : "";
                if (p_dtCauTruc.Rows[0]["KSDonVi"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSDonVi"].ToString()))
                {
                    p_sCauTruc += (p_sDonVi + p_dtCauTruc.Rows[0]["KyTuPhanCach2"].ToString()).Substring(1, Convert.ToInt32(p_dtCauTruc.Rows[0]["KSDonVi"].ToString()));
                }
                if (p_dtCauTruc.Rows[0]["KSBoPhan"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSBoPhan"].ToString()))
                {
                    p_sCauTruc += (p_sBoPhan + p_dtCauTruc.Rows[0]["KyTuPhanCach3"].ToString()).Substring(1, Convert.ToInt32(p_dtCauTruc.Rows[0]["KSBoPhan"].ToString()));
                }
                if (p_dtCauTruc.Rows[0]["KSNam"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSNam"].ToString()) && Convert.ToInt32(p_dtCauTruc.Rows[0]["KSNam"]) > 0)
                {
                    string p_sChuoiCat = ("0000000000" + p_sNam);
                    int p_iKSNam = Convert.ToInt32(p_dtCauTruc.Rows[0]["KSNam"].ToString());
                    try { p_sCauTruc += p_sChuoiCat.Substring(p_sChuoiCat.Length - p_iKSNam) + p_dtCauTruc.Rows[0]["KyTuPhanCach4"].ToString(); }
                    catch (Exception ex) { }
                }
                if (p_dtCauTruc.Rows[0]["KSThang"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSThang"].ToString()) && Convert.ToInt32(p_dtCauTruc.Rows[0]["KSThang"]) > 0)
                {
                    string p_sChuoiCat = ("0000000000" + p_sThang);
                    int p_iKSThang = Convert.ToInt32(p_dtCauTruc.Rows[0]["KSThang"].ToString());
                    try { p_sCauTruc += p_sChuoiCat.Substring(p_sChuoiCat.Length - p_iKSThang) + p_dtCauTruc.Rows[0]["KyTuPhanCach5"].ToString(); }
                    catch (Exception ex) { }
                }
                if (p_dtCauTruc.Rows[0]["KSTuDong"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSTuDong"].ToString()) && Convert.ToInt32(p_dtCauTruc.Rows[0]["KSTuDong"]) > 0)
                {
                   int p_iKSTuDong = Convert.ToInt32(p_dtCauTruc.Rows[0]["KSTuDong"].ToString());
                    string p_sDaySoKhongLay = ("0000000000").Substring(1,p_iKSTuDong);
                   p_sCauTruc += String.Format("{0:"+p_sDaySoKhongLay+"}", p_CodeId); 
                }
                return p_sCauTruc;
            }
            else
                return "";

        }
        /// <summary>
        /// User: TAMNN; 18/07/2014
        /// Mục đích: Lấy công tơ tự động theo Moule, chỉ để xem
        /// </summary>
        /// <param name="p_sMaTienTo"></param>
        /// <param name="p_sDonVi"></param>
        /// <param name="p_sBoPhan"></param>
        /// <param name="p_sNam"></param>
        /// <param name="p_sThang"></param>
        /// <returns></returns>
        public string fs_GetCodeId(string p_sMaTienTo,string p_sDonVi="",string p_sBoPhan ="",string p_sNam= "",string p_sThang ="")
        {
            Int64 p_CodeId = -1;
            SQLServer.Param[] Params = new SQLServer.Param[5];
            Params[0] = new SQLServer.Param("@p_sTienTo", p_sMaTienTo.Replace("_",""));
            Params[1] = new SQLServer.Param("@p_sDonVi", p_sDonVi);
            Params[2] = new SQLServer.Param("@p_sBoPhan", p_sBoPhan);
            Params[3] = new SQLServer.Param("@p_iNam", p_sNam);
            Params[4] = new SQLServer.Param("@p_iThang", p_sThang);
            DataTable data = Global.DataFactory.ReturnDataTable("Get_CodeID", CommandType.StoredProcedure, Params);
            if (data != null && data.Rows.Count > 0)
            {
                p_CodeId = Convert.ToInt64(data.Rows[0]["CodeID"].ToString());
            }
            else
                return "";
            // lấy cấu trúc định dạng công tơ
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select top(1) KSDonVi,KSBoPhan,KSTuDong,KSThang,KSNam,KyTuPhanCach1,KyTuPhanCach2,KyTuPhanCach3,KyTuPhanCach4,KyTuPhanCach5");
            strSQL.Append(" from HT_DinhDangCongTo a where a.MaTienTo+'_'+convert(nvarchar(20),CounterId) = @p_sMaTienTo");
            Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@p_sMaTienTo", p_sMaTienTo);
            DataTable p_dtCauTruc = Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
            if (p_dtCauTruc !=null && p_dtCauTruc.Rows.Count > 0)
            {
                string p_sCauTruc = string.Empty;
                p_sCauTruc += !string.IsNullOrEmpty(p_sMaTienTo) ? (p_sMaTienTo.Split('_').First() + p_dtCauTruc.Rows[0]["KyTuPhanCach1"].ToString()) : "";
                if (p_dtCauTruc.Rows[0]["KSDonVi"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSDonVi"].ToString()))
                {
                    p_sCauTruc += (p_sDonVi + p_dtCauTruc.Rows[0]["KyTuPhanCach2"].ToString()).Substring(1, Convert.ToInt32(p_dtCauTruc.Rows[0]["KSDonVi"].ToString()));
                }
                if (p_dtCauTruc.Rows[0]["KSBoPhan"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSBoPhan"].ToString()))
                {
                    p_sCauTruc += (p_sBoPhan + p_dtCauTruc.Rows[0]["KyTuPhanCach3"].ToString()).Substring(1, Convert.ToInt32(p_dtCauTruc.Rows[0]["KSBoPhan"].ToString()));
                }
                if (p_dtCauTruc.Rows[0]["KSNam"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSNam"].ToString()) && Convert.ToInt32(p_dtCauTruc.Rows[0]["KSNam"])>0)
                {
                    string p_sChuoiCat = ("0000000000" + p_sNam);
                    int p_iKSNam = Convert.ToInt32(p_dtCauTruc.Rows[0]["KSNam"].ToString());
                    try { p_sCauTruc += p_sChuoiCat.Substring(p_sChuoiCat.Length - p_iKSNam) + p_dtCauTruc.Rows[0]["KyTuPhanCach4"].ToString(); }
                    catch (Exception ex) { }
                }
                if (p_dtCauTruc.Rows[0]["KSThang"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSThang"].ToString()) && Convert.ToInt32(p_dtCauTruc.Rows[0]["KSThang"]) > 0)
                {
                    string p_sChuoiCat = ("0000000000" + p_sThang);
                    int p_iKSThang = Convert.ToInt32(p_dtCauTruc.Rows[0]["KSThang"].ToString());
                    try { p_sCauTruc += p_sChuoiCat.Substring(p_sChuoiCat.Length - p_iKSThang) + p_dtCauTruc.Rows[0]["KyTuPhanCach5"].ToString(); }
                    catch (Exception ex) { }
                }
                if (p_dtCauTruc.Rows[0]["KSTuDong"] != null && !string.IsNullOrEmpty(p_dtCauTruc.Rows[0]["KSTuDong"].ToString()) && Convert.ToInt32(p_dtCauTruc.Rows[0]["KSTuDong"]) > 0)
                {
                    int p_iKSTuDong = Convert.ToInt32(p_dtCauTruc.Rows[0]["KSTuDong"].ToString());
                    string p_sDaySoKhongLay = ("0000000000").Substring(1, p_iKSTuDong);
                    p_sCauTruc += String.Format("{0:" + p_sDaySoKhongLay + "}", p_CodeId);
                }
                return p_sCauTruc;
            }
            else
                return "";
        }
        /// <summary>
        /// User: TAMNN; 18/07/2014
        /// Mục đích: Lấy công tơ tự động theo Moule, chỉ để xem
        /// </summary>
        /// <param name="p_sMaTienTo"></param>
        /// <param name="p_sDonVi"></param>
        /// <param name="p_sBoPhan"></param>
        /// <param name="p_sNam"></param>
        /// <param name="p_sThang"></param>
        /// <returns></returns>
        public DataTable ft_GetDataCongToHienHanh(string p_sMaTienTo, string p_sDonVi = "", string p_sBoPhan = "", string p_sNam = "", string p_sThang = "")
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select *  from HT_CongToHienHanh_"+p_sMaTienTo.Replace("_",""));
            strSQL.Append(" where TienTo = @TienTo and DonVi = @DonVi and BoPhan = @BoPhan and Nam = @Nam and Thang = @Thang");

            SQLServer.Param[] Params = new SQLServer.Param[5];
            Params[0] = new SQLServer.Param("@TienTo", p_sMaTienTo);
            Params[1] = new SQLServer.Param("@DonVi", p_sDonVi);
            Params[2] = new SQLServer.Param("@BoPhan", p_sBoPhan);
            Params[3] = new SQLServer.Param("@Nam", p_sNam);
            Params[4] = new SQLServer.Param("@Thang", p_sThang);
            return Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
            
        }
        /// <summary>
        /// User: TAMNN 28/07/2014 (Hàm dùng chung)
        /// Mục đích: Kiểm tra dữ liệu có bị trùng trong database hay không
        /// </summary>
        /// <param name="p_sTableName"></param>
        /// <param name="p_sfill"></param>
        /// <param name="p_sGiatri"></param>
        /// <param name="p_sDieuKien"></param>
        /// <returns>Trả về một cột p_sfill trong table</returns>
        public string fs_LayDuLieuTrongTable(string p_sTableName, string p_sfill, string p_sGiatri, string p_sDieuKien=" 1=1 ")
        {
            StringBuilder strSQL = new StringBuilder(300);
            strSQL.Append("SELECT "+ p_sfill);
            strSQL.Append(" FROM "+p_sTableName);
            strSQL.Append(" WHERE " + p_sGiatri);
            strSQL.Append(" and " + p_sDieuKien);
            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString()))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        return dataReader.IsDBNull(0)?"":dataReader.GetString(0);
                    }
                }
            }
            return "";
        }
        /// <summary>
        /// User: TAMNN 28/07/2014 (Hàm dùng chung)
        /// Mục đích: Kiểm tra dữ liệu có bị trùng trong database hay không
        /// </summary>
        /// <param name="p_sTableName"></param>
        /// <param name="p_sfill"></param>
        /// <param name="p_sGiatri"></param>
        /// <param name="p_sDieuKien"></param>
        /// <returns>Trả về drow bị lặp</returns>
        public DataTable ft_LayDuLieuTrongTable(string p_sTableName,string p_sGiatri, string p_sDieuKien = " 1=1")
        {
            StringBuilder strSQL = new StringBuilder(300);
            strSQL.Append("SELECT * ");
            strSQL.Append(" FROM " + p_sTableName);
            strSQL.Append(" WHERE " + p_sGiatri);
            strSQL.Append(" and " + p_sDieuKien);
            return Global.DataFactory.GetDataTable(strSQL.ToString());
        }

        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox khoản mục
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMucForcbo(string Ma,string Name,string TableName,string DieuKien = " 1=1")
        {
            List<cboCommon> items = new List<cboCommon>();
            cboCommon item;
            StringBuilder strSQL = new StringBuilder(300);
            strSQL.Append("SELECT");
            strSQL.Append(" "+Ma+","+Name);
            strSQL.Append(" FROM " + TableName);
            strSQL.Append(" where " + DieuKien);
            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString()))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        item = new cboCommon();
                        if (dataReader.GetFieldType(0) == typeof(Int64))
                        {
                            item.Id = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetInt64(0).ToString();
                            item.Name = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
                        }
                        else
                        {
                            item.Id = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                            item.Name = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
                        }
                        items.Add(item);
                    }
                }
            }

            return items;
        }

    }
}
